package com.tony.server.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import com.google.common.collect.Lists;
import com.tony.easymvc.core.TableUtils;
import com.tony.easymvc.mapper.BizDbMapper;
import com.tony.easymvc.mapper.DbInfoMapper;
import com.tony.easymvc.mapper.HtmlOperationMapper;
import com.tony.easymvc.mapper.ProjectBizMapper;
import com.tony.easymvc.mapper.ProjectInfoMapper;
import com.tony.easymvc.mapper.ProjectTablesMapper;
import com.tony.easymvc.po.BizDb;
import com.tony.easymvc.po.BizDbExample;
import com.tony.easymvc.po.DbInfo;
import com.tony.easymvc.po.HtmlOperation;
import com.tony.easymvc.po.HtmlOperationExample;
import com.tony.easymvc.po.ProjectBiz;
import com.tony.easymvc.po.ProjectInfo;
import com.tony.easymvc.po.ProjectTables;
import com.tony.easymvc.po.ProjectTablesExample;
import com.tony.server.JDBC;
import com.tony.server.service.ProjectBizService;

@Service
public class ProjectBizServiceImpl implements ProjectBizService {
    @Resource
    ProjectBizMapper    projectBizMapper;
    @Resource
    BizDbMapper         bizDbMapper;
    @Resource
    DbInfoMapper        dbInfoMapper;
    @Resource
    ProjectInfoMapper   projectInfoMapper;
    @Resource
    ProjectTablesMapper projectTablesMapper;
    @Resource
    HtmlOperationMapper htmlOperationMapper;

    @Override
    public ProjectBiz selectProjectBizFillDb(int bizId) {
        ProjectBiz projectBiz = projectBizMapper.selectByPrimaryKey(bizId);
        Assert.notNull(projectBiz, "projectBiz 为空:" + bizId);
        BizDbExample example = new BizDbExample();
        example.or().andBizIdEqualTo(bizId);
        List<BizDb> bizDbs = bizDbMapper.selectByExample(example);

        for (int i = 0; i < bizDbs.size(); i++) {
            int ism = bizDbs.get(i).getIsMaster();
            DbInfo dbinfo = dbInfoMapper.selectByPrimaryKey(bizDbs.get(i).getDbId());
            if (ism == 1) {
                projectBiz.setMaster(dbinfo);
            } else {
                projectBiz.setSlave(dbinfo);
            }
        }
        ProjectTablesExample example1 = new ProjectTablesExample();
        example1.or().andBizIdEqualTo(projectBiz.getId());
        List<ProjectTables> tables = projectTablesMapper.selectByExample(example1);
        if (tables == null) {
            tables = Lists.newArrayList();
        }
        projectBiz.setProjectTables(tables);
        if (tables.isEmpty()) {
            return projectBiz;
        }
        for (int i = 0; i < tables.size(); i++) {
            ProjectTables pt = tables.get(i);
            List<HtmlOperation> opers = selectTableOperation(bizId, pt.getTableName());
            pt.setHtmlOperations(opers);
        }
        return projectBiz;
    }

    public List<HtmlOperation> selectTableOperation(int bizId, String tableName) {
        HtmlOperationExample example = new HtmlOperationExample();
        example.setOrderByClause("display_order");
        example.or().andBizIdEqualTo(bizId).andTableNameEqualTo(tableName);
        return htmlOperationMapper.selectByExample(example);
    }

    public List<String> selectAllTables(ProjectBiz projectBiz) throws SQLException {
        DbInfo dbinfo = projectBiz.getMaster();
        ProjectInfo projectInfo = projectInfoMapper.selectByPrimaryKey(projectBiz.getProjectId());

        JDBC jdbc = new JDBC(projectInfo.getDriverClassName(), dbinfo.getJdbcUrl(), dbinfo.getUsername(), dbinfo.getPassword());
        String dbName = TableUtils.resoleSchema(dbinfo.getJdbcUrl());
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + dbName + "'";
        System.out.println(sql);

        List<String> tableNames = Lists.newArrayList();
        try {
            conn = jdbc.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                String tableName = rs.getString(1);
                tableNames.add(tableName);
            }
        } finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(stmt);
            JdbcUtils.closeConnection(conn);
        }
        return tableNames;
    }

}
